package com.huizhonglm.diap.core.storage.db;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.huizhonglm.diap.DiapApplication;
import com.huizhonglm.diap.core.storage.model.district.CityInfo;
import com.huizhonglm.diap.core.storage.model.district.DistInfo;
import com.huizhonglm.diap.core.storage.model.district.ProvInfo;
import com.huizhonglm.diap.model.District;

import java.util.ArrayList;
import java.util.List;

/**
 * 省份、城市、地区内置数据库操作类
 *
 * @author LiXiaoLong
 * @version 1.0
 * @since 2013年7月30日 下午4:10:46 (1.0)
 */
public class DistrictDBUtil {
    private DistrictDBHelper dbm;


    public DistrictDBUtil() {
        Context context = DiapApplication.getInstance().getContext();
        dbm = new DistrictDBHelper(context);
    }

    public District getDistrict(String distId){
        District entity = new District();
        dbm.openDatabase();
        SQLiteDatabase db = dbm.getDatabase();
        try{
            String sql = "select " +
                    "a.district_id," +
                    "a.district_name," +
                    "c.city_id," +
                    "c.city_name," +
                    "c.city_code," +
                    "c.province_id," +
                    "c.province_name " +
                    "from " +
                    "district_info a " +
                    "left join city_info c on a.city_id = c.city_id " +
                    "where " +
                    "a.district_id=?";
            Cursor cursor = db.rawQuery(sql, new String[]{distId});
            while (cursor.moveToNext()) {
                entity.setProvId(cursor.getString(cursor.getColumnIndex("province_id")));
                entity.setProvName(cursor.getString(cursor.getColumnIndex("province_name")));
                entity.setCityId(cursor.getString(cursor.getColumnIndex("city_id")));
                entity.setCityName(cursor.getString(cursor.getColumnIndex("city_name")));
                entity.setCityCode(cursor.getString(cursor.getColumnIndex("city_code")));
                entity.setDistId(cursor.getString(cursor.getColumnIndex("district_id")));
                entity.setDistName(cursor.getString(cursor.getColumnIndex("district_name")));
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.close();
            dbm.closeDatabase();
        }
        return entity;
    }


    public List<ProvInfo> getProvData() {
        List<ProvInfo> provList = new ArrayList<>();
        dbm.openDatabase();
        SQLiteDatabase db = dbm.getDatabase();
        try{
            String sql = "select * from province_info";
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                ProvInfo prov = new ProvInfo();
                prov.setProvId(cursor.getString(cursor.getColumnIndex("province_id")));
                prov.setProvName(cursor.getString(cursor.getColumnIndex("province_name")));
                provList.add(prov);
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.closeDatabase();
            db.close();
        }
        return provList;
    }


    public List<CityInfo> getCityDataByProvId(String provId) {
        List<CityInfo> cityList = new ArrayList<>();
        dbm.openDatabase();
        SQLiteDatabase db = dbm.getDatabase();
        try{
            String sql = "select " +
                    "C.* " +
                    "from " +
                    "city_info C " +
                    "where " +
                    "C.province_id=? " +
                    "and C.city_id in (select A.city_id from district_info A where A.province_id=?)";
            Cursor cursor = db.rawQuery(sql, new String[]{provId, provId});
            while (cursor.moveToNext()) {
                CityInfo city = new CityInfo();
                city.setCityId(cursor.getString(cursor.getColumnIndex("city_id")));
                city.setCityName(cursor.getString(cursor.getColumnIndex("city_name")));
                city.setProvId(cursor.getString(cursor.getColumnIndex("province_id")));
                city.setCityCode(cursor.getString(cursor.getColumnIndex("city_code")));
                cityList.add(city);
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.closeDatabase();
            db.close();
        }
        return cityList;
    }


    public List<DistInfo> getDistDataByCityId(String cityId) {
        List<DistInfo> areaList = new ArrayList<>();
        dbm.openDatabase();
        SQLiteDatabase db = dbm.getDatabase();
        try{
            String sql = "select * from district_info where city_id=?";
            Cursor cursor = db.rawQuery(sql, new String[]{cityId});
            while (cursor.moveToNext()) {
                DistInfo area = new DistInfo();
                area.setDistId(cursor.getString(cursor.getColumnIndex("district_id")));
                area.setDistName(cursor.getString(cursor.getColumnIndex("district_name")));
                area.setProvId(cursor.getString(cursor.getColumnIndex("province_id")));
                area.setCityId(cursor.getString(cursor.getColumnIndex("city_id")));
                areaList.add(area);
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbm.closeDatabase();
            db.close();
        }
        return areaList;
    }

}
